[アップデート] Amazon RDS for PostgreSQL で tcn 拡張モジュールが利用出来るようになったので .NET クライアントアプリで通知を受信してみた

いわさです。

本日 RDS for PostgreSQL で tcn 拡張モジュールがサポートされたとアナウンスがされていました。

tcn 自体は以前から存在していたようですが、RDS for PostgreSQL では先日のマイナーバージョンアップデート（14.6, 13.9, 12.13, 11.18）から利用が出来るようになったということのようです。
tcn は Trigger Change Notification の略で（多分）、テーブルに関する変更を通知することが出来る組み込みの拡張モジュールです。

本日はこちらの基本的な使い方を確認しながら、.NET クライアントアプリケーションで通知を受信するということを行ってみます。

PostgreSQL における「通知」

ここでいう通知とは、PostgreSQL に従来から備わっている機能の NOTIFY と LISTEN コマンドを使うことでチャネル経由で複数のクライアント間に通知を行うことが出来る仕組みを指しています。

• NOTIFY
• LISTEN

以下のように別々のクライアントから PostgreSQL サーバーへ接続し、チャネルを経由することで特定のクライアントからの通知を複数台のクライアントで受信することが出来ます。

hogedb=> NOTIFY tcn, 'hogehogehoge';
NOTIFY
hogedb=> NOTIFY tcn, 'fugafugafuga';
NOTIFY
hogedb=> NOTIFY tcn, 'piyopiyopiyo';
NOTIFY

hogedb=> LISTEN tcn;
LISTEN
Asynchronous notification "tcn" with payload "hogehogehoge" received from server process with PID 1356.
Asynchronous notification "tcn" with payload "fugafugafuga" received from server process with PID 1356.
Asynchronous notification "tcn" with payload "piyopiyopiyo" received from server process with PID 1356.

RDS 作成して拡張有効化

では実際に RDS で試してみたいと思います。
今回はエンジンバージョンに PostgreSQL 14.6-R1 を選択しました。

tcn は本日時点の最新マイナーバージョンで利用が可能です。
バージョンごとのサポートされている拡張モジュールは以下を参照してください。

クライアントから接続し、下準備としてデータベースだけ作っておきます。

% psql -h hoge0214ctn.cpnu9ipu74g4.ap-northeast-1.rds.amazonaws.com -U postgres
Password for user postgres: 
psql (14.6 (Homebrew))
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=> create database hogedb;
CREATE DATABASE

postgres=> \c hogedb
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
You are now connected to database "hogedb" as user "postgres".
hogedb=>

tcn 設定

次に tcn の有効化やら設定やらをしていきますが、RDS のドキュメント上には何も記述はありません。
ここは PostgreSQL 公式ドキュメントを頼りに設定してみます。

この tcn は仕組みとしては組み込みのトリガー関数を AFTER EACH ROW トリガーとして動作させて、その中で先程紹介した Notify が使われている形となっているようです。
通知のペイロードにはテーブル・操作・主キーが含まれるようで、CREATE TRIGGER コマンドで設定する際にチャネルを指定出来るようですね。省略した場合は tcn がチャネル名になるようです。

なお、拡張モジュールのソースコードは以下で確認出来ます。
こちらを見る感じだと主キーが必須みたいですね。

では、テーブルを作成してトリガーを設定しましょう。
事前に Create Extension しないとトリガー設定時に以下のようにエラーになりました。

hogedb=> create table piyo (id int primary key, name2 text);
CREATE TABLE

hogedb=> create trigger piyo_tcn_trigger after insert or update or delete on piyo for each row execute function triggered_change_notification('hogechannel');
ERROR:  function triggered_change_notification() does not exist

hogedb=> CREATE EXTENSION tcn;
CREATE EXTENSION

hogedb=> create trigger piyo_tcn_trigger after insert or update or delete on piyo for each row execute function triggered_change_notification('hogechannel');
CREATE TRIGGER

通知送ってみる

準備が完了したので通知の送信と受信を行ってみましょう。
別々のクライアントから RDS for PostgreSQL へ接続しておきます。

まずはクライアントAで INSERT してみます。

hogedb=> insert into piyo values (5, '555');
INSERT 0 1
hogedb=> insert into piyo values (6, '666');
INSERT 0 1
hogedb=> insert into piyo values (7, '777');
INSERT 0 1

次にクライアントB で LISTEN してみます。

hogedb=> listen hogechannel;
LISTEN
Asynchronous notification "tcn" with payload ""piyo",I,"id"='5'" received from server process with PID 1356.
Asynchronous notification "tcn" with payload ""piyo",I,"id"='6'" received from server process with PID 1356.
Asynchronous notification "tcn" with payload ""piyo",I,"id"='7'" received from server process with PID 1356.

通知を受信することが出来ましたね。
piyo テーブルに Insert された。主キーは xx という情報が受信側では確認出来ます。

なお主キーはやはり必須のようで、主キーなしのテーブルでもトリガー設定自体は出来たのですが、INSERT 後のトリガー実行でエラーとなっていました。

hogedb=> insert into fuga values (1, 'aaa2');
ERROR:  triggered_change_notification: must be called on a table with a primary key

.NET クライアントで通知を非同期に受信してみる

通知を受信するというからには、アクティブに LISTEN コマンドを叩くのではなくてプッシュされた時に非同期で受信したいところです。

調べたところ、実際には世の中の多くの PostgreSQL クライアント・ドライバーでは非同期通知がサポートされているようなので、今回は .NET アプリケーションを作成して通知を受信出来るのか試してみました。

.NET で PostgreSQL へ接続する際には Npgsql を使うことが多いかなという印象です。
Npgsql のドキュメントにも通知を受信する仕組みが案内されています。これはいけそうですね。やってみましょう。

まずはコンソールアプリケーションを作成して Npgsql を Nuget で追加します。

% dotnet new console
The template "Console App" was created successfully.

:

% dotnet add package Npgsql --version 7.0.1
  Determining projects to restore...

:

log  : Restored /Users/iwasa.takahito/work/hoge0214dotnet/hoge0214dotnet.csproj (in 1.31 sec).

以下では接続文字列などハードコーディングしてしまっていますのでご注意ください。
先程作成した RDS for PostgreSQL へ接続させます。

また、以下のハイライト部分でチャネルを指定しています。
通知がうまく受信出来れば、6 行目のラムダ式が実行されるはず。

using Npgsql;

var connString = "Host=hoge0214ctn.cpnu9ipu74g4.ap-northeast-1.rds.amazonaws.com;Username=postgres;Password=password;Database=hogedb";
var conn = new NpgsqlConnection(connString);
conn.Open();
conn.Notification += (o, e) => Console.WriteLine("Received notification");

using (var cmd = new NpgsqlCommand("LISTEN hogechannel", conn)) {
  cmd.ExecuteNonQuery();
}

while (true) {
  conn.Wait();   // Thread will block here
}

ビルドして実行してみます。
.NET クライアントアプリケーション側は待機状態に入ります。

% dotnet build
MSBuild version 17.4.0+18d5aef85 for .NET
  Determining projects to restore...
  All projects are up-to-date for restore.
  hoge0214dotnet -> /Users/iwasa.takahito/work/hoge0214dotnet/bin/Debug/net7.0/hoge0214dotnet.dll

Build succeeded.
    0 Warning(s)
    0 Error(s)

Time Elapsed 00:00:01.64

% dotnet run

では、別の PostgreSQL クライアントからテーブルへ INSERT など行ってみましょう。

Received notification
Received notification
Received notification

ニアリアリタイムに通知を受信することが出来ました。
ここでは単純に固定文字列をコンソール出力しているだけですが、Notification イベント引数からペイロードなど様々な情報へアクセスすることが出来ます。

詳細は以下が参考になると思います。

さいごに

本日は Amazon RDS for PostgreSQL で tcn 拡張モジュールが利用出来るようになったので、基本的な検証をしつつ、最後は .NET クライアントアプリで通知を受信してみました。

アプリで実際に実装してみるとユースケースがイメージしやすいですね。
クライアントで保持しているキャッシュデータをリフレッシュするとか色々と用途は考えられそうです。

ただし、以下の RDS for PostgreSQL 14.6 のリリース通知では、リッスンしている全てのクライアントで通知を受信出来るので機密データを含むテーブルで使用しないようにするなど注意が必要と言及されていました。このあたりは注意して利用したいですね。